<?php include("header_login_required.php"); ?>



<?php

$connect = mysql_connect(DB_SERVER, DB_USER, DB_PWD);

mysql_select_db(DB_CATALOG);



$dep = $_GET['dep'];



$query = sprintf("select group_id from zt_group g

		join zt_tournament t on g.tournament_id = t.tournament_id

		where t.iscurrent = 1 and group_name = '%s'", $dep);

$result = mysql_query($query, $connect);

$row = mysql_fetch_assoc($result);

$groupId = $row['group_id'];



mysql_query("SET AUTOCOMMIT=0", $connect);

mysql_query("BEGIN", $connect);



$query = sprintf("delete from zt_team_position where team_id in

    (select t.team_id from zt_team t where t.group_id = %u)", $groupId);

mysql_query($query, $connect);

rejectTranIfError($query, "calculate_stats.php: herinitaliseren van de tabel is mislukt.");



$query = sprintf("delete from zt_player_stat where group_id = %u", $groupId);

mysql_query($query, $connect);

rejectTranIfError($query, "calculate_stats.php: herinitaliseren van de spelersstatistieken is mislukt.");



$query = sprintf("select g.*, ht.team_id AS hit, ht.team_name, ht.team_nr, vt.team_id AS vit, vt.team_name, vt.team_nr
    
    from zt_game g join zt_calendar c on

    c.calendar_id = g.calendar_id join zt_tournament t on

    c.tournament_id = t.tournament_id join zt_group gr

    on gr.group_id = g.group_id 
    
    JOIN zt_team ht ON ht.team_id = g.home_team 

    JOIN zt_team vt ON vt.team_id = g.visit_team
    
    where t.iscurrent = 1

    and gr.group_name = '%s'", $dep);

$gameResult = mysql_query($query, $connect);

rejectTranIfError($query, "calculate_stats.php: selecteren van de partijen is mislukt.");

$minBoards = 2;
if ( strnatcasecmp($dep, '1') == 0 ) $minBoards = 3;
    

if (mysql_num_rows($gameResult) > 0) {

	while ($gameRow = mysql_fetch_assoc($gameResult)) {

		$homeTeamBP = 0.0;

		$visitTeamBP = 0.0;
        
        $htForfait = false;
        $vtForfait = false;
        
        $htTournamentForfait = false;
        $vtTournamentForfait = false;
        
        // calculate inherited team forfait
        
        $query = "SELECT board_nr,index_nr_home,index_nr_visit FROM zt_game_player gp WHERE gp.game_id = ".$gameRow['game_id'];
        $boardResult = mysql_query($query, $connect);
        rejectTranIfError($query, "calculate_stats.php: selecteren van de partijdetails is mislukt.");
        while ($boardRow = mysql_fetch_assoc($boardResult)) {
            $boardnr = $boardRow['board_nr'];
            $hindex = $boardRow['index_nr_home'];
            $vindex = $boardRow['index_nr_visit'];
            if ($boardnr <= $minBoards) {
                if ($hindex == 0) {
                    $htForfait = true;
                }
                if ($vindex == 0) {
                    $vtForfait = true;   
                }       
            }
        }
        
        // lookup decided tournament forfait for team
        
        $hit = $gameRow['hit'];
        $vit = $gameRow['vit'];
        
        $query = "SELECT count(1) as forfaited FROM zt_team_forfait WHERE team_id = ".$hit;
        $forfaitResult = mysql_query($query, $connect);
        $forfaitRow = mysql_fetch_assoc($forfaitResult);
        if ($forfaitRow["forfaited"] == 1) {
            $htTournamentForfait = true;    
        }                

        
        $query = "SELECT count(1) as forfaited FROM zt_team_forfait WHERE team_id = ".$vit;
        $forfaitResult = mysql_query($query, $connect);
        $forfaitRow = mysql_fetch_assoc($forfaitResult);
        if ($forfaitRow["forfaited"] == 1) {
            $vtTournamentForfait = true;    
        }    
        

		$query = "SELECT gp.board_nr, hp.player_id AS hpId,

        hp.player_key AS hpKey, vp.player_id AS vpId,

        vp.player_key AS vpKey, rt.result, gp.home_error_id, 

        gp.visit_error_id

        FROM zt_game_player gp

        JOIN zt_game g ON g.game_id = gp.game_id

        JOIN zt_team ht ON ht.team_id = g.home_team

        JOIN club hc ON hc.club_id = ht.club_id

        JOIN zt_player_list hpl ON hpl.club_id = ht.club_id

        JOIN zt_player hp ON hp.player_list_id = hpl.player_list_id

        JOIN zt_team vt ON vt.team_id = g.visit_team

        JOIN club vc ON vc.club_id = vt.club_id

        JOIN zt_player_list vpl ON vpl.club_id = vt.club_id

        JOIN zt_player vp ON vp.player_list_id = vpl.player_list_id

        JOIN zt_result_type rt on rt.result_type_id = gp.result_type_id

        WHERE gp.game_id = ".$gameRow['game_id']."

        AND gp.index_nr_home = hp.index_nr

        AND gp.index_nr_visit = vp.index_nr

        AND hpl.player_list_id = g.home_player_list_id

        AND vpl.player_list_id = g.visit_player_list_id

        ORDER BY gp.board_nr";

		

		$hoverwriteBoardResult = "";

		$voverwriteBoardResult = "";

		$boardResult = mysql_query($query, $connect);

		rejectTranIfError($query, "calculate_stats.php: selecteren van de partijdetails is mislukt.");

		while ($boardRow = mysql_fetch_assoc($boardResult)) {

			

			$matchResult = $boardRow['result'];

			$homeErrorId = $boardRow['home_error_id'];

			$visitErrorId = $boardRow['visit_error_id'];

			$homeWon = 0;

			$drawn = 0;

			$homeLost = 0;

			

			if (isset($homeErrorId)) {

				$query = "select error_code from zt_game_player_error where error_id = ".$homeErrorId;

				$homeErrorResult = mysql_query($query, $connect);

				$homeErrorRow = mysql_fetch_assoc($homeErrorResult);

				$homeError = $homeErrorRow['error_code'];

				

				if (strnatcasecmp("invalid_order", $homeError) == 0) {

					$matchResult = "FF - 1";

					//$hoverwriteBoardResult = $matchResult;

				} else if (strnatcasecmp("team_to_low", $homeError) == 0) {

					$matchResult = "FF - 1";

					//$hoverwriteBoardResult = $matchResult;

				} else if (strnatcasecmp("wrong_team", $homeError) == 0) {

					$matchResult = "FF - 1";

					//$hoverwriteBoardResult = $matchResult;

				} else if (strnatcasecmp("inherited_forfait", $homeError) == 0) {

                    $matchResult = "FF - 1";

                    $hoverwriteBoardResult = $matchResult;

                }

			}

			

			if ( !empty($hoverwriteBoardResult) ) {

				$matchResult = $hoverwriteBoardResult;

			}

			

			if (isset($visitErrorId)) {

				$query = "select error_code from zt_game_player_error where error_id = ".$visitErrorId;

				$visitErrorResult = mysql_query($query, $connect);

				$visitErrorRow = mysql_fetch_assoc($visitErrorResult);

				$visitError = $visitErrorRow['error_code'];

				

				if (strnatcasecmp("invalid_order", $visitError) == 0) {

					$matchResult = "1 - FF";

					//$voverwriteBoardResult = $matchResult;

				} else if (strnatcasecmp("team_to_low", $visitError) == 0) {

					$matchResult = "1 - FF";

					//$voverwriteBoardResult = $matchResult;

				} else if (strnatcasecmp("wrong_team", $visitError) == 0) {

					$matchResult = "1 - FF";

					//$voverwriteBoardResult = $matchResult;

				} else if (strnatcasecmp("inherited_forfait", $visitError) == 0) {

                    $matchResult = "1 - FF";

                    $voverwriteBoardResult = $matchResult;

                }

			}

			

			if ( !empty($voverwriteBoardResult) ) {

				$matchResult = $voverwriteBoardResult;

			}



			if (strnatcasecmp("1 - 0", $matchResult) == 0) {

				$homeTeamBP += 1.0;

				$homeWon = 1;

			} else if (strnatcasecmp("5 - 5", $matchResult) == 0) {

				$homeTeamBP += 0.5;

				$visitTeamBP += 0.5;

				$drawn = 1;

			} else if (strnatcasecmp("0 - 1", $matchResult) == 0) {

				$visitTeamBP += 1.0;

				$homeLost = 1;

			} else if (strnatcasecmp("1 - FF", $matchResult) == 0) {

				$homeTeamBP += 1.0;

				$homeWon = 1;

			} else if (strnatcasecmp("FF - 1", $matchResult) == 0) {

				$visitTeamBP += 1.0;

				$homeLost = 1;

			}

            

            // calculate player stats

            

            /**

            * A performance rating (TPR) for an event is calculated by taking 

            * (1) the rating of each player beaten and adding 400, 

            * (2) the rating of each player lost to and subtracting 400, 

            * (3) the rating of each player drawn, and 

            * (4) summing these figures and dividing by the number of games played

            */

            

            $homeTotal = 0.0;

            $visitTotal = 0.0;

            if ($homeWon == 1) {

                $homeTotal = 1.0;

            } else if ($drawn == 1) {

                $homeTotal = 0.5;

                $visitTotal = 0.5;

            } else {

                $visitTotal = 1.0;

            }

            

            $homeTPR = 0.0;

            $homeAVG = 0.0;

            $visitTPR = 0.0;

            $visitAVG = 0.0;

            

            $hpKey = $boardRow['hpKey'];

                       

            $query = "select 1 from zt_player_stat where player_key = ".$hpKey." and group_id = ".$groupId;

            $playerStatResult = mysql_query($query, $connect);

            if (mysql_num_rows($playerStatResult) > 0) {

                // update

                $query = sprintf("update zt_player_stat set played = played + 1, won = won + %u,

                    drawn = drawn + %u, lost = lost + %u, total = total + %f, tpr = %f, avg = %f where player_key = %u and group_id = %u",

                    $homeWon, $drawn, $homeLost, $homeTotal, $homeTPR, $homeAVG, $hpKey, $groupId);

                mysql_query($query, $connect);

                rejectTranIfError($query, "calculate_stats.php: updaten statistieken mislukt.");

            } else {

                // insert

                $query = sprintf("insert into zt_player_stat (player_key, group_id, played, won, drawn, lost,

                    total, tpr, avg) values (%u, %u, 1, %u, %u, %u, %f, %f, %f)", $hpKey, $groupId,

                    $homeWon, $drawn, $homeLost, $homeTotal, $homeTPR, $homeAVG);

                mysql_query($query, $connect);

                rejectTranIfError($query, "calculate_stats.php: invoegen statistieken mislukt.");

            }

            

            $vpKey = $boardRow['vpKey'];

            

            $query = "select 1 from zt_player_stat where player_key = ".$vpKey." and group_id = ".$groupId;

            $playerStatResult = mysql_query($query, $connect);

            if (mysql_num_rows($playerStatResult) > 0) {

                // update

                $query = sprintf("update zt_player_stat set played = played + 1, won = won + %u,

                    drawn = drawn + %u, lost = lost + %u, total = total + %f, tpr = %f, avg = %f where player_key = %u and group_id = %u",

                    $homeLost, $drawn, $homeWon, $visitTotal, $visitTPR, $visitAVG, $vpKey, $groupId);

                mysql_query($query, $connect);

                rejectTranIfError($query, "calculate_stats.php: updaten statistieken mislukt.");

            } else {

                // insert

                $query = sprintf("insert into zt_player_stat (player_key, group_id, played, won, drawn, lost,

                    total, tpr, avg) values (%u, %u, 1, %u, %u, %u, %f, %f, %f)", $vpKey, $groupId,

                    $homeLost, $drawn, $homeWon, $visitTotal, $visitTPR, $visitAVG);

                mysql_query($query, $connect);

                rejectTranIfError($query, "calculate_stats.php: invoegen statistieken mislukt.");

            }

            

		}

		

		// search for explicit and implicit forfaits
            
        $query = "SELECT gp.board_nr, rt.result FROM zt_game_player gp

            JOIN zt_game g ON g.game_id = gp.game_id

            JOIN zt_result_type rt ON rt.result_type_id = gp.result_type_id

            WHERE gp.game_id = ".$gameRow['game_id']." AND (gp.index_nr_home = 0

            OR gp.index_nr_visit = 0) ORDER BY gp.board_nr";

        $boardResult2 = mysql_query($query, $connect);

        rejectTranIfError($query, "calculate_stats.php: selecteren van de forfaitdetails is mislukt.");

        while ($boardRow2 = mysql_fetch_assoc($boardResult2)) {

            $matchResult = $boardRow2['result'];

            if (strnatcasecmp("1 - FF", $matchResult) == 0) {

                $homeTeamBP += 1.0;

                $homeWon = 1;

            } else if (strnatcasecmp("FF - 1", $matchResult) == 0) {

                $visitTeamBP += 1.0;

                $homeLost = 1;

            }

        }
		
        $maxPoints = 4;
        if ( strnatcasecmp($dep, '1') == 0 ) $maxPoints = 6;
        
        if ( $htForfait == true ) {
            $homeTeamBP = 0.0;
            $homeWon = 0;
            $visitTeamBP = $maxPoints;
            $visitWon = 1;
        }
        
        if ( $vtForfait == true ) {
            $visitTeamBP = 0.0;
            $visitWon = 0;
            $homeTeamBP = $maxPoints;
            $homeWon = 1;
        }
        
        if ( $htTournamentForfait == true || $vtTournamentForfait == true ) {
            $homeTeamBP = 0.0;
            $homeWon = 0;
            $visitTeamBP = 0.0;
            $visitWon = 0;
        }
        
        


		$query = sprintf("select 1 from zt_team_position where team_id = %u", $gameRow['home_team']);

		$existingTeamPositionResult = mysql_query($query, $connect);

		rejectTranIfError($query, "calculate_stats.php: Opzoeken thuisploeg in tabel mislukt.");

		if (mysql_num_rows($existingTeamPositionResult) == 1) {

			$query = sprintf("update zt_team_position set bp = bp + %f,

          mp = mp + %u, games_played = games_played + 1 where team_id = %u",

			$homeTeamBP, getMatchPoints($homeTeamBP, $dep), $gameRow['home_team']);

		} else {

			$query = sprintf("insert into zt_team_position (team_id, bp, mp, games_played)

          values (%u, %f, %u, 1)", $gameRow['home_team'], $homeTeamBP,

			getMatchPoints($homeTeamBP, $dep));

		}

		

		mysql_query($query, $connect);

		rejectTranIfError($query, "calculate_stats.php: opslaan tabelgegevens thuisploeg mislukt.");



		$query = sprintf("select 1 from zt_team_position where team_id = %u", $gameRow['visit_team']);

		$existingTeamPositionResult = mysql_query($query, $connect);

		rejectTranIfError($query, "calculate_stats.php: Opzoeken bezoekersploeg in tabel is mislukt.");

		if (mysql_num_rows($existingTeamPositionResult) == 1) {

			$query = sprintf("update zt_team_position set bp = bp + %f,

          mp = mp + %u, games_played = games_played + 1 where team_id = %u",

			$visitTeamBP, getMatchPoints($visitTeamBP, $dep), $gameRow['visit_team']);

		} else {

			$query = sprintf("insert into zt_team_position (team_id, bp, mp, games_played)

          values (%u, %f, %u, 1)", $gameRow['visit_team'], $visitTeamBP,

			getMatchPoints($visitTeamBP, $dep));

		}

		

		mysql_query($query, $connect);

		rejectTranIfError($query, "calculate_stats.php: opslaan tabelgegevens bezoekers is mislukt.");



	}

}



mysql_query("COMMIT", $connect);

redirect("/tornooien/zt-php/zt_department.php?dep=".$dep);

?>



<?php include("footer.php"); ?>